package com.fingard.dsp.bank.directbank.lakala01;

import com.fingard.dsp.bank.directbank.DirectBase;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class LAKALA01Base extends DirectBase {
    public String readExcel(String filepath) throws Exception {
        try {
            Workbook wb = new HSSFWorkbook(new FileInputStream(filepath));
            Sheet sheet = wb.getSheetAt(0);
            //String dealFlag = getCellValue(sheet.getRow(1).getCell(9));

            StringBuilder sbFileContent = new StringBuilder();
            for (int i = 3; i < sheet.getLastRowNum(); i++) {
                Row detailRow = sheet.getRow(i);
                if (detailRow == null) {// 略过空行
                    continue;
                }
                int cellSize = detailRow.getLastCellNum();
                StringBuilder eachline = new StringBuilder();
                /*
                if (!dealFlag.equals(getCellValue(detailRow.getCell(0)))) {
                    continue;
                }*/
                for (int k = 0; k < cellSize; k++) {
                    Cell cell = detailRow.getCell(k);
                    if (eachline.length() > 0) {
                        eachline.append(",");
                    }
                    eachline.append(getCellValue(cell));
                }
                sbFileContent.append(eachline).append("\r\n");
            }
            return sbFileContent.toString();
        } catch (FileNotFoundException e) {
            return "";
        }
    }
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }

        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                Date theDate = cell.getDateCellValue();
                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cellValue = dff.format(theDate);
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
            }
        }

        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = dff.format(theDate);
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
